'''数据处理'''

import pymysql
from conf import settings as set


class MySQLHandler():
    # 初始化数据库对象，如果不指定参数就是默认参数
    def __init__(self, host=set.HOST, port=set.PORT, user=set.USER,
                 cursorclass=set.CURSORCLASS, charset='utf8mb4', *,
                 password=set.PASSWORD, database=set.DATABASE):
        # 初始化连接对象
        self.conn = pymysql.connect(
            user=user,
            password=password,
            host=host,
            port=port,
            db=database,
            cursorclass=cursorclass,
            charset=charset
        )
        # 初始化游标
        self.cursor = self.conn.cursor()
        self.init_table()

    @staticmethod
    def commit_sql(func):
        # 提交sql语句装饰器,负责提交事务或回滚
        def inner(*args, **kwargs):
            # 在类对象中 第一个参数为self
            # 静态方法如果想要调用self，可以通过args[0]取到self
            self = args[0]
            try:
                res = func(*args, **kwargs)
                self.conn.commit()
                return res
            except Exception as e:
                # 如果出现错误，回滚sql语句 保证数据完整性
                self.conn.rollback()
                return e
            # finally:
            # 操作结束后关闭连接
            # self.close()
            # close()函数关闭两个连接cursor连接和conn连接

        return inner

    @commit_sql
    def __init_table(self):
        # 初始化表
        sql_list = [
            """
            create table if not exists account(
            id int primary key auto_increment,
            username varchar(32) unique not null,
            password varchar(64),
            role set('student','teacher','admin') default 'student'
            );
            """
            ,
            """
            insert into account(username,password,role) values('root','dc5c7986daef50c1e02ab09b442ee34f11Sd','admin');
            """
            ,
            """
            create table if not exists school(
            id int primary key auto_increment,
            school_name varchar(32) unique not null,
            address varchar(64)
            );
            """
            ,
            """
           create table if not exists course(
           id int primary key auto_increment,
           course_name varchar(32) not null,
           course_desc text,
           school_id int,
           unique(course_name,school_id),
           foreign key(school_id) references school(id) 
           on update cascade
           on delete cascade
           );
           """
            ,
            """
            create table if not exists teacher(
            id int primary key auto_increment,
            name varchar(32) unique not null,
            age tinyint(3) unsigned,
            sex enum('male','female','other')
            );
            """
            ,
            """
            create table if not exists course2teacher(
            course_id int,
            teacher_id int,
            primary key(course_id,teacher_id),
            foreign key(course_id) references course(id) 
            on update cascade
            on delete cascade,
            foreign key(teacher_id) references teacher(id)
            on update cascade
            on delete cascade
            );
            """
            ,
            """
            create table if not exists student(
            id int primary key auto_increment,
            name varchar(32) unique not null,
            age tinyint(3) unsigned,
            sex enum('male','female','other') default 'male'
            );
            """
            ,
            """
            create table if not exists course2student(
            course_id int,
            student_id int,
            scores int default 0,
            primary key(course_id,student_id),
            foreign key(course_id) references course(id)
            on update cascade
            on delete cascade,
            foreign key(student_id) references student(id)
            on update cascade
            on delete cascade
            );
            """
        ]
        for sql in sql_list:
            self.cursor.execute(sql)

    # 查询单条数据
    @commit_sql
    def search_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    # 查询所有数据
    @commit_sql
    def search_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        results = self.cursor.fetchall()
        return results

    # 插入数据
    @commit_sql
    def insert_data(self, table_name, **kwargs):
        if kwargs:
            # 如果插入的有数据，就将数据插入
            sql = f'INSERT INTO {table_name} ({", ".join(kwargs.keys())}) VALUES {tuple(kwargs.values())}'
        else:
            # 如果没有插入数据，插入一条空数据
            sql = f'INSERT INTO {table_name} () VALUES ()'
        # print(sql)
        return self.cursor.execute(sql)

    @commit_sql
    # 更新数据
    def update_data(self, sql, **kwargs):
        res = self.cursor.execute(sql, kwargs)
        return res

    def roll_back(self):
        # 手动回滚事务
        self.conn.rollback()

    def close(self):
        self.cursor.close()
        self.conn.close()
